package com.jy.api.dao;

import com.jy.api.domain.Appraise;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.Date;
import java.util.List;

/**
 * Created by xiaoghua on 2018/11/10.
 */
public interface AppraiseDao  extends CrudRepository<Appraise,Integer>{


    Page<Appraise> findByStoreIdOrderByAppraiseTimeDesc(String storeId, Pageable pageable);

    Page<Appraise> findByStoreIdAndAppraiseTimeBetweenOrderByAppraiseTimeDesc(String storeId, Date begin,Date end,Pageable pageable);


    Page<Appraise> findByStoreIdAndAppraiseTimeBetweenAndAndEmpIdOrderByAppraiseTimeDesc(String storeId,Date begin,Date end,String empId,Pageable pageable);


    @Query(value = "SELECT  ifnull(COUNT(`id`),0)   AS `count`  ,ifnull(SUM(`appraise`),0) AS appraise FROM `appraise` WHERE `store_id` =?  and `emp_id` =? and `appraise_time` >=? and `appraise_time`<=? ORDER BY `count` DESC ", nativeQuery = true)
    List<Object[]> findSumRewardAndOrderByAmountAsc(String storeId,String empId,Date beginTime,Date endTime);

    @Query(value = "SELECT  ifnull(COUNT(`id`),0)  AS `count`,ifnull(SUM(`appraise`),0), emp_name as empName,emp_id empId FROM `appraise` WHERE `store_id` =?  group by emp_id  ORDER BY `count` DESC ", nativeQuery = true)
    List<Object[]> findIndexSumRewardAndOrderByAmountAsc(String storeId);
}
